//
//  Dao.m
//  MarketOverview
//
//  Created by madmin on 10-8-16.
//  Copyright 2010 thomsonreuters.com. All rights reserved.
//

#import "Dao.h"
#import "BabyPhotoEntry.h"

@implementation Dao

- (NSString *)dataFilePath {
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    return [documentsDirectory stringByAppendingPathComponent:kFilename];
}

static Dao *_instance = nil;

+(Dao *) getInstance {

	@synchronized(self) {
		
		if(_instance == nil) {
	
			_instance = [[Dao alloc] init];
			
		}
	}
	
	return _instance;
}

- (void)open {
	if (sqlite3_open([[self dataFilePath] UTF8String], &_pDB)
        != SQLITE_OK) {
        sqlite3_close(_pDB);
        NSAssert(0, @"Failed to open database");
    }
	
    //IF NOT EXISTS BABY TABLE
    char *errorMsg;
    NSString *createSQL = @"CREATE TABLE IF NOT EXISTS BabyActivityTable (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT,\
	CATEGORY INTEGER, TIMESTAMP DOUBLE, COMMENTS TEXT, AMOUNT INTEGER );";
    if (sqlite3_exec (_pDB, [createSQL  UTF8String],
                      NULL, NULL, &errorMsg) != SQLITE_OK) {
        sqlite3_close(_pDB);
		NSLog(@"%s", errorMsg);
        NSAssert1(0, @"Error creating table: %s", errorMsg);
	}
	
	//IF NOT EXISTS BABY GROW TABLE
    char *errorBabyGrowMsg;
    NSString *createBabyGrowSQL = @"CREATE TABLE IF NOT EXISTS BabyGrowTable (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT,\
	HEIGHT FLOAT, WEIGHT FLOAT, HEADCIR FLOAT, TIMESTAMP DOUBLE, COMMENTS TEXT,PHOTOID INTEGER);";
    if (sqlite3_exec (_pDB, [createBabyGrowSQL  UTF8String],
                      NULL, NULL, &errorBabyGrowMsg) != SQLITE_OK) {
        sqlite3_close(_pDB);
		NSLog(@"%s", errorBabyGrowMsg);
        NSAssert1(0, @"Error creating table: %s", errorBabyGrowMsg);
	}
	
	//IF NOT EXISTS Photo Table
    char *errorPhotoMsg;
    NSString *createPhotoSQL = @"CREATE TABLE IF NOT EXISTS PhotoTable (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT,THUMPATH TEXT,\
	PHOTOPATH TEXT,BABYGROWID INTEGER,CATEGORYID INTEGER, COMMENTS TEXT,TIMESTAMP DOUBLE,LATITUDE DOUBLE,LONGITUDE DOUBLE,LOCATION TEXT);";
    if (sqlite3_exec (_pDB, [createPhotoSQL  UTF8String],
                      NULL, NULL, &errorPhotoMsg) != SQLITE_OK) {
        sqlite3_close(_pDB);
		NSLog(@"%s", errorPhotoMsg);
        NSAssert1(0, @"Error creating table: %s", errorPhotoMsg);
	}
	
	//IF NOT EXISTS Photo Category Table
    char *errorPhotoCategoryMsg;
    NSString *createPhotoCategorySQL = @"CREATE TABLE IF NOT EXISTS PhotoCategoryTable (ID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT,COMMENTS TEXT,TIMESTAMP DOUBLE);";
    if (sqlite3_exec (_pDB, [createPhotoCategorySQL  UTF8String],
                      NULL, NULL, &errorPhotoCategoryMsg) != SQLITE_OK) {
        sqlite3_close(_pDB);
		NSLog(@"%s", errorPhotoCategoryMsg);
        NSAssert1(0, @"Error creating table: %s", errorPhotoCategoryMsg);
		
		//PhotoAlbumEntry *entry = [[[PhotoAlbumEntry alloc]init]autorelease];
		//entry.name     = NSLocalizedString(@"Other photo album", @"");
		//entry.comments = NSLocalizedString(@"Take baby photo anywhere", @""); 
		//entry.timestamp = [NSDate dateWithTimeIntervalSince1970:0];
		//[self InsertPhotoCategoryEntry:entry];
	}
		
	//IF NOT EXISTS Wei Bo Table
    char *errorWeiBoMsg;
    NSString *createWeiBoSQL = @"CREATE TABLE IF NOT EXISTS WeiBoTable (ID INTEGER PRIMARY KEY AUTOINCREMENT, TYPE TEXT,\
	WEIBO_ID DOUBLE, TIMESTAMP DOUBLE, ENTRY TEXT, PIC_URL TEXT );";
    if (sqlite3_exec (_pDB, [createWeiBoSQL  UTF8String],
                      NULL, NULL, &errorWeiBoMsg) != SQLITE_OK) {
        sqlite3_close(_pDB);
		NSLog(@"%s", errorWeiBoMsg);
        NSAssert1(0, @"Error creating table: %s", errorWeiBoMsg);
	}
}

- (void)close {
    if (_pDB) {
        sqlite3_close (_pDB);
        _pDB = NULL;
    }
}

- (id)init
{
    if ((self = [super init])) 
	{
		//set up default entries //thomas
		[self open];
	
//		[self RemoveAllEntries];
/*		
		[self RemoveBabyActivityWithName:@"测试"];

		BabyActivityEntry *entry = [[BabyActivityEntry alloc]init];
		entry.name      = @"测试";
		for (int i=0; i<3;i++)
		{
			entry.timestamp = [[NSDate alloc]init];
			entry.category  = i%4;
			entry.amount    = i;
			entry.comments  = @"这是一条测试哦,这是一条测试哦,这是一条测试哦"; 
			[self InsertBabyActivity:entry];
	    }

 		[self RemoveBabyGrowEntryWithName:@"测试"];

		BabyGrowEntry *growentry = [[BabyGrowEntry alloc]init];
		entry.name      = @"测试";
		for (int i=0; i<3;i++)
		{
			growentry.timestamp = [[NSDate alloc]init];
			growentry.height = 0;
			growentry.weight = 0;
			growentry.headCir = 0;
			growentry.thumPath = @"null";
			growentry.photoPath = @"null";			
			growentry.comments  = @"这是一条测试哦,这是一条测试哦,这是一条测试哦"; 
			[self InsertBabyGrowEntry:growentry];
	    }
*/		
    }
    return self;
}

-(void) QueryBabyActivityWith:(NSString *) whereClause records:(NSMutableArray *)records {
	
	NSString       *query = [NSString stringWithFormat:@"SELECT ID,NAME,CATEGORY,TIMESTAMP,COMMENTS,AMOUNT FROM BabyActivityTable %@",whereClause];

	NSLog(@"%@", query);
	sqlite3_stmt   *statement;

	if (sqlite3_prepare_v2( _pDB, [query UTF8String],-1, &statement, nil) == SQLITE_OK) 
	{
		while (sqlite3_step(statement) == SQLITE_ROW) 
		{
			BabyActivityEntry *pv = [[BabyActivityEntry alloc] autorelease];
			pv.index     = sqlite3_column_int(statement, 0);
			pv.name      = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
			pv.category  = sqlite3_column_int(statement, 2);
			pv.timestamp = [NSDate dateWithTimeIntervalSince1970:sqlite3_column_double(statement, 3)];
			pv.comments  = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 4)];
			pv.amount    = sqlite3_column_int(statement, 5);			
			[records addObject:pv];
		}
	}else {
		NSLog(@"%@ failed!");
	}
	sqlite3_finalize(statement);
}

-(void)RemoveAllEntries
{
	char *errorMsg;
	NSString *delEntry = @"DELETE FROM BabyActivityTable";
	
	if (sqlite3_exec (_pDB, [delEntry  UTF8String], NULL, NULL, &errorMsg) != SQLITE_OK) {
		sqlite3_close(_pDB);
		NSAssert1(0, @"Error in delete all entries of BabyActivity Table: %s", errorMsg);
	}

	delEntry = @"DELETE FROM BabyGrowTable";
	if (sqlite3_exec (_pDB, [delEntry  UTF8String], NULL, NULL, &errorMsg) != SQLITE_OK) {
		sqlite3_close(_pDB);
		NSAssert1(0, @"Error in delete all entries of BabyGrow Table: %s", errorMsg);
	}
	
	delEntry = @"DELETE FROM PhotoTable";
	if (sqlite3_exec (_pDB, [delEntry  UTF8String], NULL, NULL, &errorMsg) != SQLITE_OK) {
		sqlite3_close(_pDB);
		NSAssert1(0, @"Error in delete all entries of Photo Table: %s", errorMsg);
	}
}

-(void)RemoveBabyActivityWithName:(NSString *)name
{
	char *errorMsg;
	NSString *delEntry = [NSString stringWithFormat:@"DELETE FROM BabyActivityTable WHERE NAME = '%@'",name];
	
	if (sqlite3_exec (_pDB, [delEntry  UTF8String], NULL, NULL, &errorMsg) != SQLITE_OK) {
		sqlite3_close(_pDB);
		NSAssert1(0, @"Error in delete entrie of BabyActivity Table: %s", errorMsg);
	}
}

-(void)RemoveBabyActivityEntry:(BabyActivityEntry *)pv
{
	char *errorMsg;
	NSString *delEntry = [NSString stringWithFormat:@"DELETE FROM BabyActivityTable WHERE ID = '%d'",pv.index];
	NSLog(@"%@", delEntry);
	
	if (sqlite3_exec (_pDB, [delEntry  UTF8String], NULL, NULL, &errorMsg) != SQLITE_OK) {
		sqlite3_close(_pDB);
		NSAssert1(0, @"Error in delete entrie of BabyActivity Table: %s", errorMsg);
	}
}

-(void)InsertBabyActivity:(BabyActivityEntry *)entry
{
	char *errorMsg;
	double timevalue = (double)[entry.timestamp timeIntervalSince1970];
	
	NSString *insertEntry = [NSString stringWithFormat:@"INSERT INTO BabyActivityTable (\
								 NAME,CATEGORY,TIMESTAMP,COMMENTS,AMOUNT) VALUES \
								 ('%@','%d','%f','%@','%d')",
								 [[NSUserDefaults standardUserDefaults] stringForKey:@"Name"], entry.category,timevalue,entry.comments, entry.amount];
		NSLog(@"the insert sql is %@", insertEntry);
	if (sqlite3_exec (_pDB, [insertEntry  UTF8String],
						NULL, NULL, &errorMsg) != SQLITE_OK) {
		sqlite3_close(_pDB);
		NSAssert1(0, @"Error insert news to User BabyActivity Table: %s", errorMsg);
	}
}

-(void)UpdateBabyActivityEntry:(BabyActivityEntry *)entry
{
	char *errorMsg;
	NSString *updateEntry = [NSString stringWithFormat:@"UPDATE BabyActivityTable \
							 SET NAME = '%@',CATEGORY = %d, COMMENTS = '%@', AMOUNT = %d ,\
							TIMESTAMP = '%f' WHERE ID='%d' "\
							 ,[[NSUserDefaults standardUserDefaults] stringForKey:@"Name"],entry.category,entry.comments,entry.amount,[entry.timestamp timeIntervalSince1970],entry.index];
	NSLog(@"%@",updateEntry);
	if (sqlite3_exec (_pDB, [updateEntry  UTF8String],
					  NULL, NULL, &errorMsg) != SQLITE_OK) {
		sqlite3_close(_pDB);
		NSAssert1(0, @"Error updateBabyActivity %s", errorMsg);
	}
}

-(int)GetCountUnderCondition:(NSString *)columnName condition:(NSString *)condition{
	
	int ret = 0;
	
	NSString       *query = [NSString stringWithFormat: @"SELECT COUNT(%@) FROM BabyActivityTable %@", columnName, condition];
	
	NSLog(@"%@",query);
	sqlite3_stmt   *statement;
	
	if (sqlite3_prepare_v2( _pDB, [query UTF8String],
						   -1, &statement, nil) == SQLITE_OK) 
	{
		while (sqlite3_step(statement) == SQLITE_ROW) 
		{
			ret = sqlite3_column_double(statement, 0);			
		}
	}
	sqlite3_finalize(statement);
	
	return ret;
} 

-(int)GetSumUnderCondition:(NSString *)columnName condition:(NSString *)condition{
	
	int ret = 0;
	
	NSString       *query = [NSString stringWithFormat: @"SELECT SUM(%@) FROM BabyActivityTable %@", columnName, condition];
	NSLog(@"%@",query);
	
	sqlite3_stmt   *statement;
	
	if (sqlite3_prepare_v2( _pDB, [query UTF8String],
						   -1, &statement, nil) == SQLITE_OK) 
	{
		while (sqlite3_step(statement) == SQLITE_ROW) 
		{
			ret = sqlite3_column_double(statement, 0);			
		}
	}
	sqlite3_finalize(statement);
	
	return ret;
} 

-(void)SaveAllBabyEntriesToFile{
		
	NSArray        *paths  = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask,YES);
	NSString *documentDir  = [paths objectAtIndex:0];
	NSString    *fileName  = [documentDir stringByAppendingFormat:@"/iBabyNotes_Report.txt"];
	[[NSFileManager defaultManager] createFileAtPath:fileName contents:nil attributes:nil];
	NSString    *babyActivityQuery  = @"SELECT TIMESTAMP,CATEGORY,AMOUNT,COMMENTS FROM BabyActivityTable order by TIMESTAMP DESC";
	NSString    *babyGrowQuery      = @"SELECT TIMESTAMP,HEIGHT,WEIGHT,HEADCIR,COMMENTS FROM BabyGrowTable order by TIMESTAMP DESC";
	sqlite3_stmt   *statement;
	
    NSString *path=[paths objectAtIndex:0];
    NSString *filename=[path stringByAppendingPathComponent:@"DefaultValues.plist"];
    NSMutableArray *array=[[[NSMutableArray alloc] initWithContentsOfFile:filename] autorelease];
	
    NSString *tmp = [array objectAtIndex:0];
	NSLog(@"%@",tmp);
	
	NSMutableString   *HTMLHeader = [[[NSMutableString alloc] initWithString:@" "] autorelease];
	NSString *header = @"<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">"
	 "<html><head><META http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\">"
	 "<style type=\"text/css\"> "
	 "<!-- "
	 ".STYLE1 {font-size: 12px} "
	 ".STYLE2 { "
	 "font-size: 36px; "
	 "font-weight: bold;" 
	 "} "
	 "-->" 
	 "</style> "
	 "</head> "
	 "<body> "
	 "<p align=\"center\" class=\"STYLE2\">宝宝日记</p> "
	 "<div> "
	 "<table width=\"100%\" border=\"0\"> "
	 "<tr> "
	 "<td width=\"13%\" height=\"93\"><div align=\"center\"><img src=\"http://www.ibabynote.com/Adphoto/left.jpg\" width=\"180\" height=\"90\"></div></td> "
	 "<td width=\"74%\"><p align=\"center\"><span class=\"STYLE1\">数据记录由“宝宝日记” V1.2 软件提供<br> "
	 "Copyright by <a href=\"http://www.ibabynotes.com\"> 南瓜苗软件工作室</a></span></p> "
	 "<p align=\"center\"><a href=\"http://itunes.apple.com/cn/app/id461753614?mt=8\">下载“宝宝日记”手机客户端</a>|<a href=\"http://www.ibabynotes.com\"> 登陆“宝宝日记”网站</a></p></td> "
	 "<td width=\"13%\"><div align=\"center\">"
	 "<p><img src=\"http://www.ibabynote.com/Adphoto/right.jpg\" width=\"180\" height=\"90\"><br>"
	 "</p>"
	 "</div></td>"
	 "</tr>"
	 "</table>"
	 "<p>&nbsp;</p>"
	 "</div> "
	 "<div>";
	[HTMLHeader appendString:header];
	[HTMLHeader appendString:@"<div><table width=\"100%\" border=\"1\" align=\"center\" bordercolor=\"#D4D0C8\" vspace=\"40\">"];
	[HTMLHeader appendString:@"<tr bgcolor=\"#FFCBDB\">"];
	[HTMLHeader appendString:[NSString stringWithFormat:@"<th>%@</th><th>%@</th><th>%@</th><th>%@</th>",NSLocalizedString(@"Time", @""),NSLocalizedString(@"Category", @""),NSLocalizedString(@"Amount", @""),NSLocalizedString(@"Comments", @"")]];
	[HTMLHeader appendString:@"</tr>"];

//write baby activity entry	
	NSLog(@"%@",fileName);
	FILE* fp;
	if( (fp = fopen([fileName UTF8String], "w")) != nil )
	{
		fputs([HTMLHeader UTF8String],fp);
		NSDateFormatter *dateFormatter = [[NSDateFormatter alloc] init];
		[dateFormatter setDateFormat:DATE_TIME_FORMAT];
		
		if (sqlite3_prepare_v2( _pDB, [babyActivityQuery UTF8String],-1, &statement, nil) == SQLITE_OK) 
		{
			while (sqlite3_step(statement) == SQLITE_ROW) 
			{
				fputs("<tr>",fp);		
				NSString *strTime = [dateFormatter stringFromDate:[NSDate dateWithTimeIntervalSince1970:sqlite3_column_double(statement, 0)]];			
				fputs("<td>",fp);		
				fputs([[strTime description] UTF8String],fp);
				fputs("</td>",fp);		
				fputs("<td>",fp);						
				fputs([[self GetCategoryName:sqlite3_column_int(statement, 1)] UTF8String],fp);
				fputs("</td>",fp);						
				fputs("<td>",fp);						
				fputs([[NSString stringWithFormat:@"%d",sqlite3_column_int(statement, 2)] UTF8String],fp);
				fputs("</td>",fp);						
				fputs("<td>",fp);						
				fputs([[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)] UTF8String], fp);
				fputs("</td>",fp);		
				fputs("</tr>",fp);						
			}
			fputs("</table></div>",fp);						
		}
		else 
			fputs("<tr>Failed to get baby activity!</tr></table></div>",fp);						
		

//write baby grow entry
		fputs("<div><table width=\"100%\" border=\"1\" align=\"center\" bordercolor=\"#D4D0C8\" vspace=\"40\">",fp);		
		fputs("<tr bgcolor=\"#FFCBDB\">",fp);
		NSString *babyGrowTableHeader = [NSString stringWithFormat:@"<th>%@</th><th>%@</th><th>%@</th><th>%@</th><th>%@</th>",NSLocalizedString(@"Time", @""),NSLocalizedString(@"Height",@""),NSLocalizedString(@"Weight",@""),NSLocalizedString(@"HeadCir",@""),NSLocalizedString(@"Comments", @"")];
		fputs([babyGrowTableHeader UTF8String],fp);
		fputs("</tr>",fp);
		
		if (sqlite3_prepare_v2( _pDB, [babyGrowQuery UTF8String],-1, &statement, nil) == SQLITE_OK) 
		{
			while (sqlite3_step(statement) == SQLITE_ROW) 
			{
				fputs("<tr>",fp);		
				NSString *strTime = [dateFormatter stringFromDate:[NSDate dateWithTimeIntervalSince1970:sqlite3_column_double(statement, 0)]];			
				fputs("<td>",fp);		
				fputs([[strTime description] UTF8String],fp);
				fputs("</td>",fp);		
				fputs("<td>",fp);										
				fputs([[NSString stringWithFormat:@"%.1f",sqlite3_column_double(statement, 1)] UTF8String],fp);
				fputs("</td>",fp);		
				fputs("<td>",fp);						
				fputs([[NSString stringWithFormat:@"%.1f",sqlite3_column_double(statement, 2)] UTF8String],fp);
				fputs("</td>",fp);		
				fputs("<td>",fp);						
				fputs([[NSString stringWithFormat:@"%.1f",sqlite3_column_double(statement, 3)] UTF8String],fp);
				fputs("</td>",fp);		
				fputs("<td>",fp);						
				fputs([[NSString stringWithUTF8String:(char *)sqlite3_column_text(statement,4)] UTF8String], fp);
				fputs("</td>",fp);		
				fputs("</tr>",fp);				
			}
			fputs("</table></div>",fp);
		}
		else {
			fputs("<tr>Failed to get baby grow entry!</tr></table></div>",fp);						
		}
		fputs("</body></html>",fp);		
		[dateFormatter release];
		fclose(fp);
		sqlite3_finalize(statement);		
	}
}

-(void)GetReportSummaryWithStartEndDate:(NSDate *)startDate endDate:(NSDate *)endDate report:(BabyActivityReportEntry *)report{
	
	double startTimeValue = (double)[startDate timeIntervalSince1970];
	double endTimeValue   = (double)[endDate   timeIntervalSince1970];
	NSString *timeSpan    = [NSString stringWithFormat:@"where timestamp between %f and %f ",startTimeValue,endTimeValue];
	
	report.amountOfEatMum     = [self GetSumUnderCondition:@"AMOUNT"     condition:[NSString stringWithFormat:@"%@ and category = '0'",timeSpan]];
	report.countOfEatMum      = [self GetCountUnderCondition:@"CATEGORY" condition:[NSString stringWithFormat:@"%@ and category = '0'",timeSpan]];
	report.amountOfEatMilk    = [self GetSumUnderCondition:@"AMOUNT"     condition:[NSString stringWithFormat:@"%@ and category = '1'",timeSpan]];
	report.countOfEatMilk     = [self GetCountUnderCondition:@"CATEGORY" condition:[NSString stringWithFormat:@"%@ and category = '1'",timeSpan]];
	report.amountOfDrinkWater = [self GetSumUnderCondition:@"AMOUNT"     condition:[NSString stringWithFormat:@"%@ and category = '2'",timeSpan]];
	report.countOfDrinkWater  = [self GetCountUnderCondition:@"CATEGORY" condition:[NSString stringWithFormat:@"%@ and category = '2'",timeSpan]];
	report.countOfPee		   = [self GetCountUnderCondition:@"CATEGORY" condition:[NSString stringWithFormat:@"%@ and category = '3'",timeSpan]];
	report.countOfShit	       = [self GetCountUnderCondition:@"CATEGORY" condition:[NSString stringWithFormat:@"%@ and category = '4'",timeSpan]];
}

-(NSString *)GetCategoryName:(int)Category{
	switch (Category) {
		case EAT_MUM_CATEGORY:
			return NSLocalizedString(@"Mum",@"");
		case EAT_MILK_CATEGORY:
			return NSLocalizedString(@"Milk",@"");
		case DRINK_WATER_CATEGORY:
			return NSLocalizedString(@"Water",@"");
		case PEE_CATEGORY:
			return NSLocalizedString(@"Pee",@"");
		case SHIT_CATEGORY:
			return NSLocalizedString(@"Shit",@"");
		default:
			return @"";
	}
}

-(NSString *)GetAgeStringWithTwoDate:(double)fromDateTS toDateTS:(double)toDateTS{
	
	if (fromDateTS > toDateTS) {
		return nil;
	}
	else {		
		NSCalendar *gregorian = [[[NSCalendar alloc] initWithCalendarIdentifier:NSGregorianCalendar] autorelease];
		NSUInteger unitFlags =	NSHourCalendarUnit | NSMinuteCalendarUnit | 
		NSSecondCalendarUnit | NSDayCalendarUnit| NSMonthCalendarUnit | NSYearCalendarUnit;
		NSDate *fromDate = [NSDate dateWithTimeIntervalSince1970:fromDateTS];
		NSDate *toDate   = [NSDate dateWithTimeIntervalSince1970:toDateTS];
		NSDateComponents *cps = [ gregorian components:unitFlags fromDate:fromDate  toDate:toDate  options:0];
		
		NSInteger diffHour = [ cps hour ];
		NSInteger diffMin  = [ cps minute ];		
		NSInteger diffSec  = [ cps second ];
		NSInteger diffDay  = [ cps day ];
		NSInteger diffMon  = [ cps month ];
		NSInteger diffYear = [ cps year ];

		
		NSLog(  @" From Now to %@, diff: Years: %d  Months: %d, Days; %d, Hours: %d, Mins:%d, sec:%d", 			  
		[toDate description], diffYear, diffMon, diffDay, diffHour, diffMin,diffSec );
		NSString *daysStr = [[[NSString alloc] init] autorelease];
		
		if(diffYear > 0)
			daysStr = [daysStr stringByAppendingString:[NSString stringWithFormat:@"%d%@,",diffYear,NSLocalizedString(@"y(s)",@"")]];
		if(diffMon > 0)
			daysStr = [daysStr stringByAppendingString:[NSString stringWithFormat:@"%d%@",diffMon,NSLocalizedString(@"mon(s)",@"")]];
		if(diffDay > 0 || diffDay+diffMon+diffYear == 0)
			daysStr = [daysStr stringByAppendingString:[NSString stringWithFormat:@"%d %@",diffDay,NSLocalizedString(@"day(s)",@"")]];	
		return daysStr;
	}

}

-(void)LoadBabyProfile:(BabyProfile *)babyProfile{
	NSUserDefaults *prefs   = [NSUserDefaults standardUserDefaults];
	babyProfile.Name	    = [prefs stringForKey:@"Name"];
	babyProfile.Birthday    = [prefs doubleForKey:@"Birthday"];	
	babyProfile.Gender      = [prefs integerForKey:@"Gender"];

	if( babyProfile.Name == nil )
		babyProfile.Name = NSLocalizedString(@"Default baby name",@"");
	if( babyProfile.Birthday == 0 ){
		babyProfile.Birthday = [[NSDate date] timeIntervalSince1970];
	}
	NSDateFormatter *dateFormatter = [[NSDateFormatter alloc] init];
	[dateFormatter setDateFormat:DATE_FORMAT];
	babyProfile.BirthdayStr = [dateFormatter stringFromDate:[NSDate dateWithTimeIntervalSince1970:babyProfile.Birthday]];;
	[dateFormatter release];
}

-(void)GetDayReport:(NSDate *) date report:(BabyActivityReportEntry *)report{
	NSDateFormatter *dateFormatter = [[NSDateFormatter alloc] init];
	[dateFormatter setDateFormat:DATE_FORMAT];
	NSString *strTime = [dateFormatter stringFromDate:date];
	NSDate   *today   = [dateFormatter dateFromString:strTime];
	[dateFormatter release];
	NSTimeInterval secondsIn24Hours = 24 * 60 * 60;
	
	double startTimeValue = (double)[today timeIntervalSince1970];
	double endTimeValue   = (double)[[today addTimeInterval:secondsIn24Hours] timeIntervalSince1970];
	NSString *timeSpan    = [NSString stringWithFormat:@"where timestamp between %f and %f ",startTimeValue,endTimeValue];
	
	report.amountOfEatMum     = [self GetSumUnderCondition:@"AMOUNT"     condition:[NSString stringWithFormat:@"%@ and category = '0'",timeSpan]];
	report.countOfEatMum      = [self GetCountUnderCondition:@"CATEGORY" condition:[NSString stringWithFormat:@"%@ and category = '0'",timeSpan]];
	report.amountOfEatMilk    = [self GetSumUnderCondition:@"AMOUNT"     condition:[NSString stringWithFormat:@"%@ and category = '1'",timeSpan]];
	report.countOfEatMilk     = [self GetCountUnderCondition:@"CATEGORY" condition:[NSString stringWithFormat:@"%@ and category = '1'",timeSpan]];
	report.amountOfDrinkWater = [self GetSumUnderCondition:@"AMOUNT"     condition:[NSString stringWithFormat:@"%@ and category = '2'",timeSpan]];
	report.countOfDrinkWater  = [self GetCountUnderCondition:@"CATEGORY" condition:[NSString stringWithFormat:@"%@ and category = '2'",timeSpan]];
	report.countOfPee		  = [self GetCountUnderCondition:@"CATEGORY" condition:[NSString stringWithFormat:@"%@ and category = '3'",timeSpan]];
	report.countOfShit	      = [self GetCountUnderCondition:@"CATEGORY" condition:[NSString stringWithFormat:@"%@ and category = '4'",timeSpan]];
	
}

//For BabyGrowEntry
-(void)RemoveBabyGrowEntryWithName:(NSString *)name
{
	char *errorMsg;
	NSString *delEntry = [NSString stringWithFormat:@"DELETE FROM BabyGrowTable WHERE NAME = '%@'",name];
	
	if (sqlite3_exec (_pDB, [delEntry  UTF8String], NULL, NULL, &errorMsg) != SQLITE_OK) {
		sqlite3_close(_pDB);
		NSAssert1(0, @"Error in delete entrie of BabyGrowTable Table: %s", errorMsg);
	}
}

-(BabyGrowEntry *) QueryBabyGrowEntryWithGrowID:(int) ID {
	NSString       *query = [NSString stringWithFormat:@"SELECT ID,NAME,TIMESTAMP,COMMENTS,HEIGHT,WEIGHT,HEADCIR,PHOTOID FROM BabyGrowTable where ID = %d",ID];

	NSLog(@"%@", query);
	sqlite3_stmt   *statement;
	
	BabyGrowEntry *pv = [[BabyGrowEntry alloc] autorelease];
	if (sqlite3_prepare_v2( _pDB, [query UTF8String],-1, &statement, nil) == SQLITE_OK) 
	{
		while (sqlite3_step(statement) == SQLITE_ROW) 
		{			
			pv.index     = sqlite3_column_int(statement, 0);
			pv.name      = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
			pv.timestamp = [NSDate dateWithTimeIntervalSince1970:sqlite3_column_double(statement,2)];
			pv.comments  = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)];
			pv.height    = sqlite3_column_double(statement, 4);
			pv.weight    = sqlite3_column_double(statement, 5);
			pv.headCir   = sqlite3_column_double(statement, 6);
			pv.photoID   = sqlite3_column_int(statement, 7);
		}
	}else {
		NSLog(@"%@ failed!");
		sqlite3_finalize(statement);
		return nil;
	}
	sqlite3_finalize(statement);
	return pv;
}



-(void) QueryBabyGrowEntryWith:(NSString *) whereClause records:(NSMutableArray *)records {
	
	//NSString       *query = [NSString stringWithFormat:@"SELECT ID,NAME,TIMESTAMP,COMMENTS,HEIGHT,WEIGHT,HEADCIR,PHOTOPATH,THUMPATH FROM BabyGrowTable %@",whereClause];
	NSString       *query = [NSString stringWithFormat:@"SELECT BabyGrowTable.ID,BabyGrowTable.NAME,BabyGrowTable.TIMESTAMP,BabyGrowTable.COMMENTS,BabyGrowTable.HEIGHT,BabyGrowTable.WEIGHT,BabyGrowTable.HEADCIR, \
							 PhotoTable.THUMPATH,PhotoTable.PHOTOPATH,PhotoTable.ID FROM BabyGrowTable,PhotoTable %@",whereClause];
	NSLog(@"%@", query);
	sqlite3_stmt   *statement;
	
	if (sqlite3_prepare_v2( _pDB, [query UTF8String],-1, &statement, nil) == SQLITE_OK) 
	{
		while (sqlite3_step(statement) == SQLITE_ROW) 
		{
			BabyGrowEntry *pv = [[BabyGrowEntry alloc] autorelease];
			pv.index     = sqlite3_column_int(statement, 0);
			pv.name      = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
			pv.timestamp = [NSDate dateWithTimeIntervalSince1970:sqlite3_column_double(statement,2)];
			pv.comments  = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)];
			pv.height    = sqlite3_column_double(statement, 4);
			pv.weight    = sqlite3_column_double(statement, 5);
			pv.headCir   = sqlite3_column_double(statement, 6);
			pv.thumPath  = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 7)];
			pv.photoPath = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 8)];
			pv.photoID   = sqlite3_column_int(statement, 9);
			[records addObject:pv];
		}
	}else {
		NSLog(@"%@ failed!");
	}
	sqlite3_finalize(statement);
}


-(void) QueryOnlyBabyGrowEntryWith:(NSString *) whereClause records:(NSMutableArray *)records {
	
	NSString       *query = [NSString stringWithFormat:@"SELECT ID,NAME,BabyGrowTable.TIMESTAMP,COMMENTS,HEIGHT,WEIGHT,HEADCIR FROM BabyGrowTable %@",whereClause];
	NSLog(@"%@", query);
	sqlite3_stmt   *statement;
	
	if (sqlite3_prepare_v2( _pDB, [query UTF8String],-1, &statement, nil) == SQLITE_OK) 
	{
		while (sqlite3_step(statement) == SQLITE_ROW) 
		{
			BabyGrowEntry *pv = [[BabyGrowEntry alloc] autorelease];
			pv.index     = sqlite3_column_int(statement, 0);
			pv.name      = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
			pv.timestamp = [NSDate dateWithTimeIntervalSince1970:sqlite3_column_double(statement,2)];
			pv.comments  = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)];
			pv.height    = sqlite3_column_double(statement, 4);
			pv.weight    = sqlite3_column_double(statement, 5);
			pv.headCir   = sqlite3_column_double(statement, 6);
			[records addObject:pv];
		}
	}else {
		NSLog(@"%@ failed!");
	}
	sqlite3_finalize(statement);
}
 
-(int)InsertBabyGrowEntry:(BabyGrowEntry *)entry
{
	char *errorMsg;
	double timevalue = (double)[entry.timestamp timeIntervalSince1970];

	int babyGrowID = 0;
	int photoID = 0;
	//insert baby grow entry first
	NSString *insertEntry = [NSString stringWithFormat:@"INSERT INTO BabyGrowTable (\
							 NAME,TIMESTAMP,COMMENTS,HEIGHT,WEIGHT,HEADCIR) VALUES \
							 ('%@','%f','%@','%f','%f','%f')",
							 [[NSUserDefaults standardUserDefaults] stringForKey:@"Name"], timevalue,entry.comments, entry.height,entry.weight,entry.headCir];
	NSLog(@"the insert sql is %@", insertEntry);
	if (sqlite3_exec (_pDB, [insertEntry  UTF8String],
					  NULL, NULL, &errorMsg) != SQLITE_OK) {
		sqlite3_close(_pDB);
		NSAssert1(0, @"Error insert BabyGrowEntry %s", errorMsg);
	}
	
	NSString *queryEntry = [NSString stringWithFormat:@"SELECT ID FROM BabyGrowTable WHERE TIMESTAMP = '%f'",timevalue];
	sqlite3_stmt   *statement;
	if (sqlite3_prepare_v2( _pDB, [queryEntry UTF8String],
						   -1, &statement, nil) == SQLITE_OK) 
	{
		while (sqlite3_step(statement) == SQLITE_ROW) 
		{
			babyGrowID = sqlite3_column_int(statement, 0);			
		}
	}
	sqlite3_finalize(statement);
	
	//insert photo entry
	insertEntry = [NSString stringWithFormat:@"INSERT INTO PhotoTable (\
							 NAME,THUMPATH,PHOTOPATH,BABYGROWID,TIMESTAMP,CATEGORYID,COMMENTS,latitude,longitude,location) VALUES \
							 ('%@','%@','%@','%d','%f','-1','%@','0','0','null')",
							 entry.name,entry.thumPath,entry.photoPath,babyGrowID,timevalue,entry.comments];
	NSLog(@"the insert sql is %@", insertEntry);
	if (sqlite3_exec (_pDB, [insertEntry  UTF8String],
					  NULL, NULL, &errorMsg) != SQLITE_OK) {
		sqlite3_close(_pDB);
		NSAssert1(0, @"Error insert PhotoEntry %s", errorMsg);
	}
	
	NSLog(@"%@",insertEntry);
	queryEntry = [NSString stringWithFormat:@"SELECT ID FROM PhotoTable WHERE TIMESTAMP = '%f'",timevalue];
	if (sqlite3_prepare_v2( _pDB, [queryEntry UTF8String],
						   -1, &statement, nil) == SQLITE_OK) 
	{
		while (sqlite3_step(statement) == SQLITE_ROW) 
		{
			photoID = sqlite3_column_int(statement, 0);			
		}
	}
	sqlite3_finalize(statement);
	
	//update baby grow entry with new photo entry
	NSString *updateEntry = [NSString stringWithFormat:@"UPDATE BabyGrowTable \
							 SET PHOTOID = '%d' \
							 WHERE ID = '%d'"
							 ,photoID,babyGrowID];
	NSLog(@"%@",updateEntry);
	if (sqlite3_exec (_pDB, [updateEntry  UTF8String],
					  NULL, NULL, &errorMsg) != SQLITE_OK) {
		sqlite3_close(_pDB);
		NSAssert1(0, @"Error update BabyGrowEntry %s", errorMsg);
	}
	
	entry.index = babyGrowID;
	entry.photoID = photoID;
	
	return babyGrowID;
}

-(void)UpdateBabyGrowEntry:(BabyGrowEntry *)entry
{
	double timevalue = (double)[entry.timestamp timeIntervalSince1970];
	char *errorMsg;
	NSString *updateEntry = [NSString stringWithFormat:@"UPDATE BabyGrowTable \
							 SET NAME = '%@',TIMESTAMP = '%f', COMMENTS = '%@', HEIGHT = '%f',WEIGHT = '%f', HEADCIR = '%f' \
							 WHERE ID = '%d'"\
							 ,[[NSUserDefaults standardUserDefaults] stringForKey:@"Name"],timevalue,entry.comments,entry.height,entry.weight,entry.headCir,entry.index];
	NSLog(@"%@",updateEntry);
	if (sqlite3_exec (_pDB, [updateEntry  UTF8String],
					  NULL, NULL, &errorMsg) != SQLITE_OK) {
		sqlite3_close(_pDB);
		NSAssert1(0, @"Error update BabyGrowEntry %s", errorMsg);
	}
	
	updateEntry = [NSString stringWithFormat:@"UPDATE PhotoTable \
							 SET THUMPATH = '%@', PHOTOPATH = '%@', COMMENTS = '%@' \
							 WHERE ID = '%d'"\
							 ,entry.thumPath,entry.photoPath,[entry getSummaryString],entry.photoID];
	NSLog(@"%@",updateEntry);
	if (sqlite3_exec (_pDB, [updateEntry  UTF8String],
					  NULL, NULL, &errorMsg) != SQLITE_OK) {
		sqlite3_close(_pDB);
		NSAssert1(0, @"Error update BabyGrowEntry %s", errorMsg);
	}
	
}

-(void)RemoveBabyGrowEntry:(BabyGrowEntry *)entry
{
	char *errorMsg;
	NSString *delEntry = [NSString stringWithFormat:@"DELETE FROM BabyGrowTable WHERE ID = '%d'",entry.index];
	NSLog(@"%@", delEntry);
	
	if (sqlite3_exec (_pDB, [delEntry  UTF8String], NULL, NULL, &errorMsg) != SQLITE_OK) {
		sqlite3_close(_pDB);
		NSAssert1(0, @"Error in delete entrie of Baby Grow Table: %s", errorMsg);
	}
	
	delEntry = [NSString stringWithFormat:@"DELETE FROM PhotoTable WHERE ID = '%d'",entry.photoID];
	NSLog(@"%@", delEntry);
	
	if (sqlite3_exec (_pDB, [delEntry  UTF8String], NULL, NULL, &errorMsg) != SQLITE_OK) {
		sqlite3_close(_pDB);
		NSAssert1(0, @"Error in delete entrie of Baby Grow Table: %s", errorMsg);
	}
}

//For BabyPhoto Entry
-(void)QueryBabyPhotoEntryWith:(NSString *) whereClause records:(NSMutableArray *)records{
	
	NSString       *query = [NSString stringWithFormat:@"SELECT ID,NAME,THUMPATH,PHOTOPATH,COMMENTS,TIMESTAMP,BABYGROWID,CATEGORYID,LATITUDE,LONGITUDE,LOCATION FROM PhotoTable %@",whereClause];

	NSLog(@"%@", query);
	sqlite3_stmt   *statement;
	
	if (sqlite3_prepare_v2( _pDB, [query UTF8String],-1, &statement, nil) == SQLITE_OK) 
	{
		while (sqlite3_step(statement) == SQLITE_ROW) 
		{
			BabyPhotoEntry *pv = [[BabyPhotoEntry alloc] autorelease];
			pv.index          = sqlite3_column_int(statement, 0);
			pv.name        = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
			pv.thumPath    = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)];
			pv.photoPath   = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 3)];			
			pv.comments    = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 4)];						
			pv.timestamp   = [NSDate dateWithTimeIntervalSince1970:sqlite3_column_double(statement,5)];
			pv.babyGrowID  = sqlite3_column_double(statement, 6);
			pv.categoryID  = sqlite3_column_double(statement, 7);
			pv.latitude    = sqlite3_column_double(statement, 8);
			pv.longitude   = sqlite3_column_double(statement, 9);
			pv.location    = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 10)];			
			[records addObject:pv];
		}
	}else {
		NSLog(@"%@ failed!");
	}
	sqlite3_finalize(statement);
}

-(int)InsertBabyPhotoEntry:(BabyPhotoEntry *)entry
{
	char *errorMsg;
	double timevalue = (double)[entry.timestamp timeIntervalSince1970];

	NSString *insertEntry = [NSString stringWithFormat:@"INSERT INTO PhotoTable (\
							 NAME,THUMPATH,PHOTOPATH,BABYGROWID,CATEGORYID,TIMESTAMP,COMMENTS,LATITUDE,LONGITUDE,LOCATION) VALUES \
							 ('%@','%@','%@','%d','%d','%f','%@','%f','%f','%@')",
							 [[NSUserDefaults standardUserDefaults] stringForKey:@"Name"], entry.thumPath,entry.photoPath,entry.babyGrowID,entry.categoryID,timevalue,entry.comments,entry.latitude,entry.longitude,entry.location];					
	NSLog(@"the insert sql is %@", insertEntry);
	if (sqlite3_exec (_pDB, [insertEntry  UTF8String],
					  NULL, NULL, &errorMsg) != SQLITE_OK) {
		sqlite3_close(_pDB);
		NSAssert1(0, @"Error insert BabyGrowEntry %s", errorMsg);
	}
	
	int babyPhotoID = 0;
	NSString *queryEntry = [NSString stringWithFormat:@"SELECT ID FROM PhotoTable WHERE TIMESTAMP = '%f'",timevalue];
	sqlite3_stmt   *statement;
	if (sqlite3_prepare_v2( _pDB, [queryEntry UTF8String],
						   -1, &statement, nil) == SQLITE_OK) 
	{
		while (sqlite3_step(statement) == SQLITE_ROW) 
		{
			babyPhotoID = sqlite3_column_int(statement, 0);			
		}
	}
	sqlite3_finalize(statement);
	
	return babyPhotoID;
}

-(void)UpdateBabyPhotoEntry:(BabyPhotoEntry *)entry
{
	double timevalue = (double)[entry.timestamp timeIntervalSince1970];
	char *errorMsg;

	NSString *updateEntry = [NSString stringWithFormat:@"UPDATE PhotoTable \
				   SET THUMPATH = '%@', PHOTOPATH = '%@', COMMENTS = '%@', CATEGORYID = '%d',TIMESTAMP = '%f',\
				   LATITUDE = '%f', LONGITUDE = '%f', LOCATION = '%@'\
				   WHERE ID = '%d'"\
				   ,entry.thumPath,entry.photoPath,entry.comments,entry.categoryID,timevalue,entry.latitude,entry.longitude,entry.location,entry.index];
	NSLog(@"%@",updateEntry);
	if (sqlite3_exec (_pDB, [updateEntry  UTF8String],
					  NULL, NULL, &errorMsg) != SQLITE_OK) {
		sqlite3_close(_pDB);
		NSAssert1(0, @"Error update BabyGrowEntry %s", errorMsg);
	}
}

-(void)RemoveBabyPhotoEntry:(BabyPhotoEntry *)entry
{
	char *errorMsg;
	NSString *delEntry = [NSString stringWithFormat:@"DELETE FROM PhotoTable WHERE ID = '%d'",entry.index];
	NSLog(@"%@", delEntry);
	
	if (sqlite3_exec (_pDB, [delEntry  UTF8String], NULL, NULL, &errorMsg) != SQLITE_OK) {
		sqlite3_close(_pDB);
		NSAssert1(0, @"Error in delete entrie of Baby Photo Table: %s", errorMsg);
	}
}

//For Photo Category Entry
-(void)QueryPhotoCategoryEntryWith:(NSString *) whereClause records:(NSMutableArray *)records{
	
	NSString       *query = [NSString stringWithFormat:@"SELECT ID,NAME,COMMENTS,TIMESTAMP FROM PhotoCategoryTable %@",whereClause];
	
	NSLog(@"%@", query);
	sqlite3_stmt   *statement;
	
	if (sqlite3_prepare_v2( _pDB, [query UTF8String],-1, &statement, nil) == SQLITE_OK) 
	{
		while (sqlite3_step(statement) == SQLITE_ROW) 
		{
			PhotoAlbumEntry *pv = [[PhotoAlbumEntry alloc] autorelease];
			pv.index          = sqlite3_column_int(statement, 0);
			pv.name        = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];
			pv.comments    = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 2)];						
			pv.timestamp   = [NSDate dateWithTimeIntervalSince1970:sqlite3_column_double(statement,3)];
			[records addObject:pv];
		}
	}else {
		NSLog(@"%@ failed!");
	}
	sqlite3_finalize(statement);
}

-(void)InsertPhotoCategoryEntry:(PhotoAlbumEntry *)entry
{
	char *errorMsg;
	double timevalue = (double)[entry.timestamp timeIntervalSince1970];
	
	NSString *insertEntry = [NSString stringWithFormat:@"INSERT INTO PhotoCategoryTable (\
							 NAME,TIMESTAMP,COMMENTS) VALUES \
							 ('%@','%f','%@')",
							 entry.name,timevalue,entry.comments];					
	NSLog(@"the insert sql is %@", insertEntry);
	if (sqlite3_exec (_pDB, [insertEntry  UTF8String],
					  NULL, NULL, &errorMsg) != SQLITE_OK) {
		sqlite3_close(_pDB);
		NSAssert1(0, @"Error insert Photo Category %s", errorMsg);
	}
}

-(void)UpdatePhotoCategoryEntry:(PhotoAlbumEntry *)entry
{
	double timevalue = (double)[entry.timestamp timeIntervalSince1970];
	char *errorMsg;
	
	NSString *updateEntry = [NSString stringWithFormat:@"UPDATE PhotoCategoryTable \
							 SET NAME = '%@', COMMENTS = '%@',TIMESTAMP = '%f',\
							 WHERE ID = '%d'"
							 ,entry.name,entry.comments,timevalue,entry.index];
	NSLog(@"%@",updateEntry);
	if (sqlite3_exec (_pDB, [updateEntry  UTF8String],
					  NULL, NULL, &errorMsg) != SQLITE_OK) {
		sqlite3_close(_pDB);
		NSAssert1(0, @"Error update Photo Category %s", errorMsg);
	}
}

-(void)RemovePhotoCategoryEntry:(PhotoAlbumEntry *)entry
{
	char *errorMsg;
	NSString *delEntry = [NSString stringWithFormat:@"DELETE FROM PhotoCategoryTable WHERE ID = '%d'",entry.index];
	NSLog(@"%@", delEntry);
	
	if (sqlite3_exec (_pDB, [delEntry  UTF8String], NULL, NULL, &errorMsg) != SQLITE_OK) {
		sqlite3_close(_pDB);
		NSAssert1(0, @"Error in delete entrie of Photo Category Table: %s", errorMsg);
	}
}
@end
